package com.jr.dao.impl;

import com.jr.dao.IEmpSalUsersDao;
import com.jr.pojo2.EmpSalUsers;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;

import java.sql.*;
import java.util.ArrayList;

public class EmpSalUsersDaoImpl implements IEmpSalUsersDao {

    DBHelper db = new DBHelper();

    //添加员工 -- 同时添加： 一条user数据，一条salaries数据
    @Override
    public int insertEmp(EmpSalUsers emp) {
        String sqlEmp = "insert into employees values(null,?,?,?,?,?,?,?)";
        /*(Integer employeeId, String name, Integer departmentId,
                     Integer positionId, Date hireDate, Timestamp createdAt,
                     Timestamp updatedAt, Integer age)*/
        Object[] obj = {emp.getName(),emp.getDepartmentId(),
                emp.getPositionId(), emp.getHireDate(),new Timestamp(System.currentTimeMillis()),
                new Timestamp(System.currentTimeMillis()),emp.getAge()};
        return db.dtUpdate(sqlEmp,obj);
    }

    @Override //修改一条
    public int updateEmp(EmpSalUsers emp) {
        String sqlEmp = "update from employees set name=?,age=?,positionId=?,departmentId=?,hireDate=?,updatedAt=? where employeeId=?";
        Object[] objEmp = {emp.getName(),emp.getAge(),emp.getPositionId(),emp.getDepartmentId(),emp.getHireDate(),new Timestamp(System.currentTimeMillis()),emp.getEmployeeId()};
        int updateEmp = db.dtUpdate(sqlEmp, objEmp);
        String sqlUser = "update users set phoneNumber=?,email=? where employeeId=?";
        Object[] objUser = {emp.getPhoneNumber(),emp.getEmail(),emp.getEmployeeId()};
        int updateUser = db.dtUpdate(sqlUser, objUser);
        return updateEmp+updateUser;
    }

    @Override //按 员工ID--employeeId 查询 一条数据
    public EmpSalUsers selectByEmpId(Integer employeeId) {
        String sql = "select e.employeeId,name,age,positionId,departmentId,hireDate,baseSalary,phoneNumber,email from employees e" +
                " inner join salaries s on e.employeeId=s.employeeId" +
                " inner join users u on e.employeeId=u.employeeId" +
                " where e.employeeId=?";
        ArrayList<EmpSalUsers> list = db.dtQuery(sql,EmpSalUsers.class,employeeId);
        return list.size()>0?list.get(0):new EmpSalUsers(0,"",null,0,0,null,null,null,null);
    }

    @Override //删除员工 -- 同时删除users表里的数据（测试删除数据的id：6~15）
    public int deleteEmpByEmpId(Integer employeeId) {
        String sql = "delete from employees where employeeId=?";
        return db.dtUpdate(sql, employeeId);
    }

    @Override //分页查询 -- 总条数 -- 按 员工姓名 + 部门
    public int selectPageDataCountByNameDep(String searchEmp) {
        StringBuilder sql = new StringBuilder("select count(e.employeeId) from employees e, departments d" +
                " where (e.name like ? and e.departmentId=d.departmentId)" +
                " or (d.name like ? and e.departmentId=d.departmentId)");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());

            ps.setString(1, "%" + searchEmp + "%");
            ps.setString(2, "%" + searchEmp + "%");

            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeAll(con,ps,resultSet);
        }
        return 0;
    }

    @Override // 分页查询 -- 展示集合-- 按 员工姓名 + 部门
    public ArrayList<EmpSalUsers> selectIndexDateByNameDep(String searchEmp, PageHelper phEmp) {
        String sql = "select count(e.employeeId) from employees e, departments d" +
                " where (e.name like ? and e.departmentId=d.departmentId)" +
                " or (d.name like ? and e.departmentId=d.departmentId)";
        int index = ((phEmp.getPageIndex()-1)*phEmp.getShowNumber());

        return db.dtQuery(sql,EmpSalUsers.class,"%"+searchEmp+"%","%"+searchEmp+"%",index,phEmp.getShowNumber());
    }
}
